package com.doubleblacktech.fxa
{
	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.errors.SQLError;
	import flash.events.Event;
	import flash.events.EventDispatcher;
	import flash.filesystem.File;
	import flash.utils.*;
	
	import mx.collections.ArrayCollection;
	import mx.controls.Alert;
	import mx.managers.CursorManager;

	[Event(name="procError", type="flash.events.Event")]
	[Event(name="procComplete", type="FxAProcResult")]
	
	public class FxAProc extends EventDispatcher
	{   
		private var _xmlSQLText:XML;
		private var _dbFile:String; 
		private var _dataset:ArrayCollection;
		private var _setWaitCursor:Boolean = false;
		private var _standardDir:String;
		private var _resultObj:SQLResult;
		
		protected var conn:SQLConnection;
		
		private var _dbResults:ArrayCollection;
		private var _dbDir:File;
		public var sqlStmt:SQLStatement;
		private var _sqltxt:XML;
		
		/**
		 *	Constructor 
		 */		
		public function FxAProc () {}
		
		/**
		 * execProcedure()
		 * @param dbDir
		 * @param db
		 * @param dbRes
		 * 
		 */		
		public function execProcedure(dbDir:String = null, db:String = null, dbRes:ArrayCollection = null):void  {
			if(db != null) {
				dbFile = db;
			}
			
			if(db != null) {
				_dbDir = File[dbDir];
			}
				
			if(createConnection()) {
				if(dbRes != null) {
					dataset = dbRes;
				}

				if(_setWaitCursor) {
					CursorManager.setBusyCursor();
				}

				createProc();
   			}
		}
		
		/**
		 * createConnection()
		 * @return Boolean
		 * 
		 */		
		private function createConnection():Boolean {
			var res:Boolean = true;
			
			if (conn == null) {
				conn = new SQLConnection(true);

				try{
					var connDBFile:File = _dbDir.resolvePath(dbFile);
				
					conn.open(connDBFile);
			
					if(!conn.connected) {
						throw new Error ("Unable to make a connection to " + dbFile);
					}
					
					sqlStmt = new SQLStatement();
				
					sqlStmt.sqlConnection = conn;
					
					res = true;
				} catch(error:SQLError) {
					throw new Error (error.message + ": " + dbFile);
					
					res = false;
				}
			}
			
			return res;
		}
		
		/**
		 *	createProc()
		 *	Overridden in the inherited class
		 */		
		public function createProc():void {
		}
		
		/**
		 *	execStmt() 
		 * 
		 */		
		private function execStmt():void {
			var key:String;
			
			sqlStmt.text = xmlSQLText.sqltext.text();
			
			sqlStmt.clearParameters();
			
			for each (var sqlParam:XML in xmlSQLText.children()){
				if (sqlParam.name() == "params"){
					sqlStmt.parameters[":" + sqlParam.attribute("name")] = String(sqlParam.attribute("value"));
				}
			}
				
			sqlStmt.execute();
			
			var result:SQLResult = sqlStmt.getResult();
					
			if (result.data != null) {	
				_dbResults = new ArrayCollection(result.data);
			}
			
			_resultObj = result
		}
		
		protected function procEnd():void {
			if(_setWaitCursor) {
				CursorManager.removeBusyCursor();
			}
			
			var fxaProcResultVO:FxAProcResultVO = new FxAProcResultVO;
			
			fxaProcResultVO.data = _dbResults;
            
            var fxaProcResult:FxAProcResult = new FxAProcResult("procComplete");
			fxaProcResult.result = fxaProcResultVO;
			
			dispatchEvent(fxaProcResult);	
		}
		
		/**
		 * 
		 * Getters and Setters 
		 * 
		 */		
		public function get xmlSQLText():XML {
			return _xmlSQLText;
		}
		
		public function set xmlSQLText(value:XML):void {
			_xmlSQLText = value;
			
			if(_xmlSQLText) {
				execStmt();
			}
		}
		
		public function get dbFile():String {
			return _dbFile;
		}
		public function set dbFile(value:String):void {
			_dbFile = value;
		}
		
		public function get dataset():ArrayCollection {
			return _dataset;
		}
		public function set dataset(value:ArrayCollection):void {
			_dataset = value;
		}
		
		public function get setWaitCursor():Boolean {
			return _setWaitCursor;
		}
		
		public function set setWaitCursor(value:Boolean):void {
			_setWaitCursor = value;
		}

		public function get standardDir():String {
			return _standardDir;
		}
		
		// Valid values include; applicationStorageDirectory,desktopDirectory,userDirectory,documentsDirectory 
		public function set standardDir(value:String):void {
			_standardDir = value;
			_dbDir = File[value];
		}
		
		public function get resultObj():SQLResult {
			return _resultObj;
		}
	}
}